        function getEodSalesMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.1 EOD Step 1: getEodSalesMetrics - Fetching sales metrics';

            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            $sql = "
                SELECT
                    COUNT(*) AS order_count,
                    COALESCE(SUM(order_total_original_gross - customer_service_fee_gross), 0) AS gross_sales,
                    COALESCE(SUM(order_total_original_gross), 0) AS customer_paid_total,
                    COALESCE(SUM(stripe_fee_total), 0) AS stripe_fees,
                    COALESCE(SUM(platform_fee_gross), 0) AS platform_fees,
                    COALESCE(SUM(customer_service_fee_gross), 0) AS platform_customer_fees,
                    COALESCE(SUM(delivery_fee_gross), 0) AS delivery_fees,
                    COALESCE(SUM(customer_service_fee_gross), 0) AS customer_service_fee_gross,
                    COALESCE(SUM(small_order_fee_gross), 0) AS small_order_fees
                FROM food_order
                WHERE tenant_id = :tenant_id
                AND created_at >= :window_start
                AND created_at < :window_end
                AND payment_status IN ('paid', 'refunded', 'partial_refund')
            ";

            $stmt = $db->prepare($sql);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);

            $stmt->execute();
            $data = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (!$data) {
                $data = [
                    'order_count' => 0,
                    'gross_sales' => 0,
                    'customer_paid_total' => 0,
                    'stripe_fees' => 0,
                    'platform_fees' => 0,
                    'platform_customer_fees' => 0,
                    'delivery_fees' => 0,
                    'customer_service_fee_gross' => 0,
                    'small_order_fees' => 0
                ];
            }

            return ['status' => 'success', 'data' => $data];
        }
        function getEodFullRefundMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.2 EOD Step 2: getEodFullRefundMetrics - Fetching full refund metrics';

            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            $sql = "
                SELECT
                    COUNT(DISTINCT fre.refund_group_id) AS full_refund_count,
                    COALESCE(SUM(fo.order_total_original_gross), 0) AS full_refund_total,
                    COALESCE(SUM(fo.order_total_original_gross - fo.customer_service_fee_gross), 0) AS full_refund_tenant_total,
                    COALESCE(SUM(fo.platform_fee_gross + fo.customer_service_fee_gross), 0) AS tenant_and_customer_fee_refunds,
                    COALESCE(SUM(fo.platform_fee_gross), 0) AS platform_fee_gross_refunds,
                    COALESCE(SUM(fo.customer_service_fee_gross), 0) AS customer_service_fee_gross_refunds,
                    COALESCE(SUM(fo.delivery_fee_gross), 0) AS delivery_fee_refunds,
                    COALESCE(SUM(fo.small_order_fee_gross), 0) AS small_order_fee_refunds,
                    COALESCE(SUM(fo.stripe_fee_total), 0) AS stripe_fee_refund_impact
                FROM food_order_refund_event fre
                INNER JOIN food_order fo
                    ON fo.id = fre.order_id
                WHERE fre.tenant_id = :tenant_id
                AND fre.processed_at >= :window_start
                AND fre.processed_at < :window_end
                AND fre.refund_type = 'full'
            ";

            $stmt = $db->prepare($sql);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);

            $stmt->execute();
            $data = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (!$data) {
                $data = [
                    'full_refund_count' => 0,
                    'full_refund_total' => 0,
                    'full_refund_tenant_total' => 0, 
                    'tenant_and_customer_fee_refunds' => 0, // platform_refunds renamed to tenant_and_customer_fee_refunds
                    'platform_fee_gross_refunds' => 0,
                    'customer_service_fee_gross_refunds' => 0,
                    'delivery_fee_refunds' => 0,
                    'small_order_fee_refunds' => 0
                ];
            }

            return ['status' => 'success', 'data' => $data];
        }
        function getEodItemRefundMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.3 EOD Step 3: getEodItemRefundMetrics - Fetching item refund metrics';

            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            $sql = "
                SELECT
                    COALESCE(SUM(gross_pence), 0) AS item_refund_total,
                    COALESCE(SUM(net_pence), 0) AS item_refund_net_total,
                    COALESCE(SUM(vat_pence), 0) AS item_refund_vat_total
                FROM food_order_refund_event
                WHERE tenant_id = :tenant_id
                AND processed_at >= :window_start
                AND processed_at < :window_end
                AND refund_type = 'item'
            ";

            $stmt = $db->prepare($sql);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);

            $stmt->execute();
            $data = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (!$data) {
                $data = [
                    'item_refund_total' => 0,
                    'item_refund_net_total' => 0,
                    'item_refund_vat_total' => 0
                ];
            }

            return ['status' => 'success', 'data' => $data];
        }
        function getEodPartialRefundMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.4 EOD Step 4: getEodPartialRefundMetrics - Fetching partial refund metrics';

            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            $sql = "
                SELECT
                    COUNT(DISTINCT refund_group_id) AS partial_refund_count,
                    COALESCE(SUM(gross_pence), 0) AS partial_refund_total,
                    COALESCE(SUM(net_pence), 0) AS partial_refund_net_total,
                    COALESCE(SUM(vat_pence), 0) AS partial_refund_vat_total
                FROM food_order_refund_event
                WHERE tenant_id = :tenant_id
                AND processed_at >= :window_start
                AND processed_at < :window_end
                AND refund_type = 'partial'
            ";

            $stmt = $db->prepare($sql);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);

            $stmt->execute();
            $data = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (!$data) {
                $data = [
                    'partial_refund_count' => 0,
                    'partial_refund_total' => 0,
                    'partial_refund_net_total' => 0,
                    'partial_refund_vat_total' => 0
                ];
            }

            return ['status' => 'success', 'data' => $data];
        }
        function getEodSalesVatMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.5 EOD Step 5: getEodSalesVatMetrics - Fetching sales VAT metrics';

            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            $sql = "
                SELECT
                    COALESCE(SUM(fovs.vat_gross_original), 0) AS tenant_sales_vat_gross,
                    COALESCE(SUM(fovs.vat_net_original), 0) AS tenant_sales_vat_net,
                    COALESCE(SUM(fovs.vat_amount_original), 0) AS tenant_sales_vat
                FROM food_order_vat_summary fovs
                INNER JOIN food_order fo
                    ON fo.id = fovs.order_id
                WHERE fo.tenant_id = :tenant_id
                AND fo.created_at >= :window_start
                AND fo.created_at < :window_end
                AND fo.payment_status IN ('paid', 'refunded', 'partial_refund')
            ";

            $stmt = $db->prepare($sql);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);

            $stmt->execute();
            $data = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (!$data) {
                $data = [
                    'tenant_sales_vat_gross' => 0,
                    'tenant_sales_vat_net' => 0,
                    'tenant_sales_vat' => 0
                ];
            }

            return ['status' => 'success', 'data' => $data];
        }
        function getEodRefundVatMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.6 EOD Step 6: getEodRefundVatMetrics - Fetching refund VAT metrics';

            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            $sqlFull = "
                SELECT
                    COALESCE(SUM(fovs.vat_gross_original), 0) AS full_refund_vat_gross,
                    COALESCE(SUM(fovs.vat_net_original), 0) AS full_refund_vat_net,
                    COALESCE(SUM(fovs.vat_amount_original), 0) AS full_refund_vat
                FROM food_order_vat_summary fovs
                INNER JOIN (
                    SELECT DISTINCT order_id
                    FROM food_order_refund_event
                    WHERE tenant_id = :tenant_id
                    AND processed_at >= :window_start
                    AND processed_at < :window_end
                    AND refund_type = 'full'
                ) fr
                    ON fr.order_id = fovs.order_id
            ";

            $stmtFull = $db->prepare($sqlFull);
            $stmtFull->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmtFull->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmtFull->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);
            $stmtFull->execute();
            $fullData = $stmtFull->fetch(\PDO::FETCH_ASSOC);

            if (!$fullData) {
                $fullData = [
                    'full_refund_vat_gross' => 0,
                    'full_refund_vat_net' => 0,
                    'full_refund_vat' => 0
                ];
            }

            $sqlItemPartial = "
                SELECT
                    COALESCE(SUM(gross_pence), 0) AS item_partial_refund_vat_gross,
                    COALESCE(SUM(net_pence), 0) AS item_partial_refund_vat_net,
                    COALESCE(SUM(vat_pence), 0) AS item_partial_refund_vat
                FROM food_order_refund_event
                WHERE tenant_id = :tenant_id
                AND processed_at >= :window_start
                AND processed_at < :window_end
                AND refund_type IN ('item', 'partial')
            ";

            $stmtItemPartial = $db->prepare($sqlItemPartial);
            $stmtItemPartial->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmtItemPartial->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmtItemPartial->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);
            $stmtItemPartial->execute();
            $itemPartialData = $stmtItemPartial->fetch(\PDO::FETCH_ASSOC);

            if (!$itemPartialData) {
                $itemPartialData = [
                    'item_partial_refund_vat_gross' => 0,
                    'item_partial_refund_vat_net' => 0,
                    'item_partial_refund_vat' => 0
                ];
            }

            $data = [
                'full_refund_vat_gross' => (int)$fullData['full_refund_vat_gross'],
                'full_refund_vat_net' => (int)$fullData['full_refund_vat_net'],
                'full_refund_vat' => (int)$fullData['full_refund_vat'],

                'item_partial_refund_vat_gross' => (int)$itemPartialData['item_partial_refund_vat_gross'],
                'item_partial_refund_vat_net' => (int)$itemPartialData['item_partial_refund_vat_net'],
                'item_partial_refund_vat' => (int)$itemPartialData['item_partial_refund_vat'],

                'tenant_refund_vat_gross' => (int)$fullData['full_refund_vat_gross'] + (int)$itemPartialData['item_partial_refund_vat_gross'],
                'tenant_refund_vat_net' => (int)$fullData['full_refund_vat_net'] + (int)$itemPartialData['item_partial_refund_vat_net'],
                'tenant_refund_vat' => (int)$fullData['full_refund_vat'] + (int)$itemPartialData['item_partial_refund_vat']
            ];

            return ['status' => 'success', 'data' => $data];
        }
        function getEodStripeImpactMetrics(&$inputData) {
            $inputData['step'][] = 'step 1.6.B.7 EOD Step 7: getEodStripeImpactMetrics - Fetching Stripe impact metrics';
            $db = $inputData['db']['dbApp'];
            $tenantId = $inputData['tenantId'] ?? null;
            $windowStart = $inputData['windowStart'] ?? null;
            $windowEnd = $inputData['windowEnd'] ?? null;

            if (!$tenantId) {
                return ['status' => 'failed', 'message' => 'tenantId is required'];
            }

            if (!$windowStart || !$windowEnd) {
                return ['status' => 'failed', 'message' => 'windowStart and windowEnd are required'];
            }

            // 1. Stripe on orders created in this window
            $sqlSales = "
                SELECT COALESCE(SUM(stripe_fee_total), 0) AS sales_stripe_fees_in_window
                FROM food_order
                WHERE tenant_id = :tenant_id
                AND created_at >= :window_start
                AND created_at < :window_end
                AND payment_status IN ('paid', 'refunded', 'partial_refund')
            ";

            // 2. Stripe on orders that had a full refund processed in this window
            $sqlRefund = "
                SELECT COALESCE(SUM(fo.stripe_fee_total), 0) AS refund_stripe_fees_in_window
                FROM food_order_refund_event fre
                INNER JOIN food_order fo
                    ON fo.id = fre.order_id
                WHERE fre.tenant_id = :tenant_id
                AND fre.processed_at >= :window_start
                AND fre.processed_at < :window_end
                AND fre.refund_type = 'full'
            ";

            // 3. Overlap: orders both created in this window AND fully refunded in this window
            $sqlOverlap = "
                SELECT COALESCE(SUM(fo.stripe_fee_total), 0) AS same_window_refund_overlap_stripe_fees
                FROM food_order fo
                INNER JOIN food_order_refund_event fre
                    ON fo.id = fre.order_id
                WHERE fo.tenant_id = :tenant_id
                AND fo.created_at >= :window_start
                AND fo.created_at < :window_end
                AND fre.processed_at >= :window_start
                AND fre.processed_at < :window_end
                AND fre.refund_type = 'full'
            ";

            $stmt = $db->prepare($sqlSales);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);
            $stmt->execute();
            $sales = (int)($stmt->fetch(\PDO::FETCH_ASSOC)['sales_stripe_fees_in_window'] ?? 0);

            $stmt = $db->prepare($sqlRefund);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);
            $stmt->execute();
            $refund = (int)($stmt->fetch(\PDO::FETCH_ASSOC)['refund_stripe_fees_in_window'] ?? 0);

            $stmt = $db->prepare($sqlOverlap);
            $stmt->bindValue(':tenant_id', (int)$tenantId, \PDO::PARAM_INT);
            $stmt->bindValue(':window_start', $windowStart, \PDO::PARAM_STR);
            $stmt->bindValue(':window_end', $windowEnd, \PDO::PARAM_STR);
            $stmt->execute();
            $overlap = (int)($stmt->fetch(\PDO::FETCH_ASSOC)['same_window_refund_overlap_stripe_fees'] ?? 0);

            return [
                'status' => 'success',
                'data' => [
                    'sales_stripe_fees_in_window' => $sales,
                    'refund_stripe_fees_in_window' => $refund,
                    'same_window_refund_overlap_stripe_fees' => $overlap
                ]
            ];
        }